﻿USE [TanNamChinh]
/*
Cập nhật CSDL ngày 25-12-2013
*/
ALTER TABLE [dbo].[tblTruckAccessory]
Add IsActive bit null
go

--Xóa các column isAttach không dùng
ALTER TABLE [dbo].[tblHBL]
	DROP COLUMN IsDescriptionAttachList, IsMarkNumberAttachList, IsContAttachList
go

--Tạo delete cascade tblFileMBL với tblFile
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblFileMBL_tblFile]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFileMBL]'))
ALTER TABLE [dbo].[tblFileMBL] DROP CONSTRAINT [FK_tblFileMBL_tblFile]
go
ALTER TABLE [dbo].[tblFileMBL]  WITH CHECK ADD  CONSTRAINT [FK_tblFileMBL_tblFile] FOREIGN KEY([ID_File])
REFERENCES [dbo].[tblFile] ([ID_File])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblFileMBL] CHECK CONSTRAINT [FK_tblFileMBL_tblFile]
GO

--Tạo delete cascade tblHBL với tblComercialInvoice
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblComercialInvoice_tblHBL]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblComercialInvoice]'))
ALTER TABLE [dbo].[tblComercialInvoice] DROP CONSTRAINT [FK_tblComercialInvoice_tblHBL]
GO
ALTER TABLE [dbo].[tblComercialInvoice]  WITH CHECK ADD  CONSTRAINT [FK_tblComercialInvoice_tblHBL] FOREIGN KEY([ID_HBL])
REFERENCES [dbo].[tblHBL] ([ID_HBL])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblComercialInvoice] CHECK CONSTRAINT [FK_tblComercialInvoice_tblHBL]
GO


--Bỏ delete cascade tblMBL với tblFileMBL
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblFileMBL_tblMBL]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFileMBL]'))
ALTER TABLE [dbo].[tblFileMBL] DROP CONSTRAINT [FK_tblFileMBL_tblMBL]
ALTER TABLE [dbo].[tblFileMBL]  WITH CHECK ADD  CONSTRAINT [FK_tblFileMBL_tblMBL] FOREIGN KEY([ID_MBL])
REFERENCES [dbo].[tblMBL] ([ID_MBL])
GO
ALTER TABLE [dbo].[tblFileMBL] CHECK CONSTRAINT [FK_tblFileMBL_tblMBL]
go

--Bỏ viewHT_Databases không dùng
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[viewHT_Databases]'))
DROP VIEW [dbo].[viewHT_Databases]
go


--Bỏ HT_GetRemainMBLList không dùng
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HT_GetRemainMBLList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[HT_GetRemainMBLList]
go

--Bỏ tblCompany_ForeignCP không dùng
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblCompany_ForeignCP_tblCompany]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblCompany_ForeignCP]'))
ALTER TABLE [dbo].[tblCompany_ForeignCP] DROP CONSTRAINT [FK_tblCompany_ForeignCP_tblCompany]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblCompany_ForeignCP_tblForeignCompany]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblCompany_ForeignCP]'))
ALTER TABLE [dbo].[tblCompany_ForeignCP] DROP CONSTRAINT [FK_tblCompany_ForeignCP_tblForeignCompany]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblCompany_ForeignCP_tlbForeignCompany]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblCompany_ForeignCP]'))
ALTER TABLE [dbo].[tblCompany_ForeignCP] DROP CONSTRAINT [FK_tblCompany_ForeignCP_tlbForeignCompany]
GO
/****** Object:  Table [dbo].[tblCompany_ForeignCP]    Script Date: 12/26/2013 21:50:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblCompany_ForeignCP]') AND type in (N'U'))
DROP TABLE [dbo].[tblCompany_ForeignCP]
GO

--Cập nhật store procedure HT_ResetDB
/* =============================================
	Author:		Tuan
	Create date: 16-09-2013
	Update date: 25-12-2013
	Description:	
		Reset lại toàn bộ CSDL
		Những bảng dữ liệu nghiệp vụ như tblFile, tblFileMBL, tblMBL, ... sẽ xóa hết
		Những bảng danh mục sẽ xóa record có isActive = 0
 ============================================= */
ALTER PROCEDURE [dbo].[HT_ResetDB]
AS
	--Xóa các bảng dữ liệu File
	truncate table tblFile
	truncate table tblFileFee
	truncate table tblFileMBL
	truncate table tblFixProgress
	truncate table FreightTariff
	truncate table tblHBL
	truncate table tblLog
	truncate table tblMBL
	truncate table tblOilFuel
	truncate table tblOilPayment
	truncate table tblOilUnitPrice
	truncate table tblPaymentVoucher
	truncate table tblAdvanceVoucher
	truncate table tblComercialInvoice
	truncate table tblCont
	truncate table tblContFee
	truncate table tblReceiptVoucher
	truncate table tblRecentBackup
	truncate table tblSellingFee
	truncate table tblTaxReturn
	truncate table tblTotalMonth
	truncate table tblVoyage
	truncate table tblVoyageFee
	
	--Xóa các danh mục có IsActive = 0
	delete from tblAccessory where isActive = 0
	delete from tblAgent where isActive = 0
	delete from tblCompany where isActive = 0
	delete from tblConType where isActive = 0
	delete from tblCountry where isActive = 0
	delete from tblCurrency where isActive = 0
	delete from tblCustomer where isActive = 0
	delete from tblDemDet where isActive = 0
	delete from tblDriver where isActive = 0
	delete from tblFee where isActive = 0
	delete from tblDemDet where isActive = 0
	delete from tblForeignCompany where isActive = 0
	delete from tblGood where isActive = 0
	delete from tblLine where isActive = 0
	delete from tblLineCont where isActive = 0
	delete from tblPort where isActive = 0
	delete from tblTruck where isActive = 0
	delete from tblTruckAccessory where isActive = 0
	delete from tblUnit where isActive = 0
	delete from tblVessel where isActive = 0
